package chelydra.sharelydra.client.database;

/**
 *
 * @author gauthiercart
 */

import chelydra.sharelydra.client.friend.Notification;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import java.util.Properties;



public class DatabaseManager {

  private String framework = "embedded";
  private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  private String protocol = "jdbc:derby:";
	private String dbName = "SharelydraDB";

	private Connection conn = null;
	private Properties props;
	private Map<Statement,Date> statements;

  public static void main(String[] args) {

		DatabaseManager DbManager = new DatabaseManager();

		DbManager.eraseEmbeddedDatabase();

		DbManager.createEmbeddedDatabase();

		//DbManager.addNewFile( "American Gangster.avi", "/Users/gauthiercart/Desktop/Video", 983183886);
		//DbManager.addNewFile( "American History X.avi", "/Users/gauthiercart/Desktop/Video", 671348736);
		//DbManager.addNewFile( "Hot shot 1.avi", "/Users/gauthiercart/Desktop/Video", 693716992);
    //DbManager.addNewFile( "Hot shot 2.avi", "/Users/gauthiercart/Desktop/Video", 726405120);
    //DbManager.deleteFile("Hot shot 2.avi");
		//System.out.println(DbManager.lookForAFile("Hot shot"));
    //System.out.println(DbManager.getFileList());
		//System.out.println(DbManager.getFileInfoById(3));
		//DbManager.deleteSharedFileList();

//		DbManager.setUserInformation("CART", "Gauthier", "cartgau", "gaukaist", "gauthier.cart@hotmail.fr", "gaugau");
	//	Map<String,String> getinformation = DbManager.getUserInformation();
		//System.err.println(getinformation);
		//System.out.println(getinformation.get("UserPassword"));
    //DbManager.updateUserInformation("SIMO", "Franck", "franck.simo@utt.fr", "francky la menace");
		//System.out.println(DbManager.existingUser());

		//DbManager.addNewGroup("KAIST");
		//DbManager.addNewGroup("Friend");
		//DbManager.deleteGroup("KAIST");
		//DbManager.deleteGroup("Friend");
		//DbManager.updateGroup("Family", "Friend");
		//System.out.println(DbManager.getGroups());

		//DbManager.addNewGroupMember("Friend", "Benjamin73");
		//DbManager.addNewGroupMember("Friend", "Momo");
		//DbManager.addNewGroupMember("Friend", "Pefounet");
    //DbManager.addNewGroupMember("Friend", "Francky");
		//DbManager.addNewGroupMember("KAIST", "Latortue");
		//DbManager.addNewGroupMember("Friend", "Latortue");
		//DbManager.deleteFriend("Latortue");
		//DbManager.deleteGroupMembers("KAIST","Latortue");
		//System.out.println(DbManager.getLoginsOfGroup("Friend"));
		//System.out.println(DbManager.getNoLoginOfGroup("KAIST"));

    //DbManager.addNewNotification("Benjamin73", "cartgaut", "Share", "Into the KAIST");
		//DbManager.addNewNotification("cartgaut", "pefounet", "Start download", "Into the librairy");
    //System.out.println(DbManager.getLast15Notification());
  }

	/**
	 * Basic Constructor
	 */
	public DatabaseManager() {
		//Connection to the database
		System.out.println("Connecting to the " + this.framework + " database");
    this.loadDriver();

		this.props = new Properties();
		this.props.put("user", "root");
		this.props.put("password", "root");

		try {
			this.conn = DriverManager.getConnection(this.protocol + this.dbName + ";create=true", this.props);
			System.out.println("Connected to database " + this.dbName);
      this.conn.setAutoCommit(false);
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
		}

		this.statements = new Hashtable();
	}

	public void addNewNotification(Notification note){

		//List of Statements and PreparedStatements
    PreparedStatement psInsert = null;

    try {
      psInsert = this.conn.prepareStatement("INSERT INTO Notification (NotificationContent, NotificationType, NotificationDate, NotificationReceiver, NotificationSenderLogin, NotificationSenderNickname) VALUES (?, ?, ?, ?, ?, ?)");
      this.addStatement(psInsert);

			//String notificationDate = longDate.format(currentDate).toString();

      psInsert.setString(	1, note.getContent()							);
			psInsert.setInt(		2, note.getType()									);
			psInsert.setLong(		3, note.getCreatedTime().getTime());
			psInsert.setString(	4, note.getReceiverLogin()				);
			psInsert.setString(	5, note.getSenderLogin()					);
			psInsert.setString(	6, note.getSenderNickname()				);

      psInsert.executeUpdate();
      System.out.println("Notification inserted:");

      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void addNewNotifications(Collection<Notification> notes){

		PreparedStatement psInsert = null;

    try {
      psInsert = conn.prepareStatement("INSERT INTO Notification (NotificationContent, NotificationType, NotificationDate, NotificationReceiver, NotificationSenderLogin, NotificationSenderNickname) VALUES (?, ?, ?, ?, ?, ?)");
      this.addStatement(psInsert);

			//String notificationDate = longDate.format(currentDate).toString();

			Iterator ite = notes.iterator();
			while (ite.hasNext()) {
				Notification note = (Notification)ite.next();
				
				psInsert.setString(	1, note.getContent()							);
				psInsert.setInt(		2, note.getType()									);
				psInsert.setLong(		3, note.getCreatedTime().getTime());
				psInsert.setString(	4, note.getReceiverLogin()				);
				psInsert.setString(	5, note.getSenderLogin()					);
				psInsert.setString(	6, note.getSenderNickname()				);

				psInsert.executeUpdate();
				System.out.println("Notification inserted:");
			}
		
      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public Collection<Notification> getLast15Notifications(){

    //Initialisation of variables and arrays
		Collection<Notification> colNotifications = new Vector();

    Integer i= 0;

    Statement s = null;
    ResultSet rs = null;

    try {

      //Preparation of a SQL-Select request which select all the data of the table File
      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT NotificationID, NotificationSenderLogin, NotificationSenderNickname, NotificationReceiver, NotificationType, NotificationContent, NotificationDate FROM Notification ORDER BY NotificationID DESC";

			//Execution of the request
			rs = s.executeQuery(querySQL);

			for (i=0; i<15; i++){

				while(rs.next()){

						//Map<String, String> result = new Hashtable();

						//String notificationID       = rs.getString(1);
						String notificationSenderLogin   = rs.getString("NotificationSenderLogin");
						String notificationSenderNickname   = rs.getString("NotificationSenderNickname");
						String notificationReceiver = rs.getString("NotificationReceiver");
						Integer notificationType     = new Integer(rs.getInt("NotificationType"));
						String notificationContent  = rs.getString("NotificationContent");
						Date notificationDate     =	new Date();
						notificationDate.setTime(rs.getLong("NotificationDate"));

						/*result.put("NotificationID"      , notificationID      );
						result.put("NotificationSender"  , notificationSender  );
						result.put("NotificationReceiver", notificationReceiver);
						result.put("NotificationType"    , notificationType    );
						result.put("NotificationContent" , notificationContent );
						result.put("NotificationDate"    , notificationDate    );*/
						//public Notification(Date createdTime, Integer type, String content, String senderLogin, String senderNickname, String receiverLogin)
						Notification note = new Notification(notificationDate, notificationType,
										notificationContent, notificationSenderLogin, notificationSenderNickname, notificationReceiver);

						colNotifications.add(note);
			  }
			}

      rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return colNotifications;
	}
	
	public Notification getNotificationForTypeTime(Integer type, Long time){

		Notification note = null;

    Statement s = null;
    ResultSet rs = null;

    try {

      //Preparation of a SQL-Select request which select all the data of the table File
      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT NotificationID, NotificationSenderLogin, NotificationSenderNickname, NotificationReceiver, NotificationType, NotificationContent, NotificationDate FROM Notification WHERE NotificationDate="+time+" AND NotificationType="+type;

			//Execution of the request
			rs = s.executeQuery(querySQL);

			if (rs.next()){
				String notificationSenderLogin   = rs.getString("NotificationSenderLogin");
				String notificationSenderNickname   = rs.getString("NotificationSenderNickname");
				String notificationReceiver = rs.getString("NotificationReceiver");
				Integer notificationType     = new Integer(rs.getInt("NotificationType"));
				String notificationContent  = rs.getString("NotificationContent");
				Date notificationDate     =	new Date();
				notificationDate.setTime(rs.getLong("NotificationDate"));

				note = new Notification(notificationDate, notificationType, notificationContent,
								notificationSenderLogin, notificationSenderNickname, notificationReceiver);
			}

      rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return note;
	}

	public void deleteNotificationForTypeTime(Integer type, Long time){

		Statement s = null;

    try {

      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "DELETE FROM Notification WHERE NotificationDate="+time+" AND NotificationType="+type;

			//Execution of the request
			s.executeUpdate(querySQL);

      //Commit and shut down the database
      this.conn.commit();
		} catch(SQLException sqle) {
			System.out.println(sqle);
    }
	}

	public void addNewGroup(String groupName){
		PreparedStatement psInsert = null;

    try {
      psInsert = conn.prepareStatement("INSERT INTO Groups (GroupName) VALUES(?)");
      this.addStatement(psInsert);

      psInsert.setString(1, groupName );
      psInsert.executeUpdate();
      System.out.println("Data inserted:"+ groupName);

      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void updateGroup(String groupName, String newGroupName ){
		PreparedStatement psInsert = null;

    try {
      psInsert = conn.prepareStatement("UPDATE Groups SET GroupName = ? WHERE GroupName = ?");
      this.addStatement(psInsert);

      psInsert.setString(1, newGroupName );
			psInsert.setString(2, groupName );
      psInsert.executeUpdate();
      System.out.println("Data inserted:"+ newGroupName);

      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void deleteGroup(String groupName){
		PreparedStatement psDelete = null;
		PreparedStatement psDeleteGroupMembers = null;
    Statement s = null;
    ResultSet rs = null;

    try {
      String groupID = null;

			s = this.conn.createStatement();
      this.addStatement(s);

      String querySQL = "SELECT GroupId From Groups Where GroupName ='" + groupName + "'";

			//Execution of the request
			rs = s.executeQuery(querySQL);

			while(rs.next()){
				groupID = rs.getString(1);
			}

			psDeleteGroupMembers = conn.prepareStatement("DELETE FROM GroupMembers WHERE GroupId = ? ");
			this.addStatement(psDeleteGroupMembers);

			psDeleteGroupMembers.setString(1, groupID );
      psDeleteGroupMembers.executeUpdate();
      System.out.println("Data deleted GroupMembers from:"+ groupName);


      psDelete = conn.prepareStatement("DELETE FROM Groups WHERE GroupName = ? ");
      this.addStatement(psDelete);

      psDelete.setString(1, groupName );
      psDelete.executeUpdate();
      System.out.println("Data deleted:"+ groupName);

			rs.close();
      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void deleteGroupMembers(String groupName, String groupMemberslogin){
		ResultSet rs = null;
		String groupID = null;

    PreparedStatement psDelete = null;
    Statement s = null;

    try {
			s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT GroupID FROM Groups WHERE GroupName = '" + groupName +"'";
			rs = s.executeQuery(querySQL);

			if (rs.next()) {
				groupID   = rs.getString(1);
			}

      psDelete = conn.prepareStatement("DELETE FROM GroupMembers WHERE GroupMembersLogin = ? AND GroupID = ? ");
      this.addStatement(psDelete);

      psDelete.setString(1, groupMemberslogin );
			psDelete.setString(2, groupID );
      psDelete.executeUpdate();
      System.out.println("Data deleted:"+ groupMemberslogin);

			rs.close();
      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public Map<String,Integer> getGroups() {

    //Initialisation of variables and arrays
		Map<String,Integer> groups = new Hashtable();

    int GroupMembersNumber = 0;

    Statement s = null;
		Statement j = null;
    ResultSet rs = null;
		ResultSet rsGroupMembers =null;

    try {

      //Preparation of a SQL-Select request which select all the data of the table File
      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT GroupID, GroupName FROM Groups";

			//Execution of the request
			rs = s.executeQuery(querySQL);

			//Each data of the matching tuples are put into a Hashtable and these Hashtable are put into a collection
			while(rs.next()) {

				GroupMembersNumber = 0;

				String GroupID   = rs.getString(1);
        String GroupName = rs.getString(2);

				j = this.conn.createStatement();
        this.addStatement(j);
				//String querySQLGroupMember = "SELECT GroupMembersID, GroupMembersLogin FROM GroupMembers WHERE GroupID = " + GroupID;
				String querySQLGroupMember = "SELECT COUNT(GroupMembersID) FROM GroupMembers WHERE GroupID = " + GroupID;

				//Execution of the request
				rsGroupMembers = j.executeQuery(querySQLGroupMember);

				if (rsGroupMembers.next())
					GroupMembersNumber = rsGroupMembers.getInt(1);

				groups.put(GroupName, GroupMembersNumber);
				rsGroupMembers.close();
			}

			rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return groups;
  }

	public boolean groupAlreadyExists(String groupName) {
		Map<String,Integer> groups = this.getGroups();
		return groups.containsKey(groupName);
	}

	public void addNewGroupMember(String groupName, String groupMemberLogin){
		PreparedStatement psInsert = null;
		PreparedStatement psSelect = null;
		ResultSet rs = null;

    try {
			psSelect = conn.prepareStatement("SELECT GroupID FROM Groups WHERE GroupName = ? ");
      this.addStatement(psSelect);

      psSelect.setString(1, groupName );
      rs = psSelect.executeQuery();

			String groupId = null;

			while(rs.next()){

				groupId = rs.getString(1);
			}

      psInsert = conn.prepareStatement("INSERT INTO GroupMembers (GroupId, GroupMembersLogin) VALUES(?, ?)");
      this.addStatement(psInsert);

      psInsert.setString(1, groupId );
			psInsert.setString(2, groupMemberLogin );
      psInsert.executeUpdate();
      System.out.println("Data inserted:"+ groupMemberLogin);

			rs.close();
      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public Collection<String> getLoginsOfGroup(String groupName){

		Collection<String> colLogin = new Vector();

    Statement s = null;
		Statement j = null;
    ResultSet rs = null;
		ResultSet rsGroupMembersLogin =null;

    try {

      //Preparation of a SQL-Select request which select all the data of the table File
      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT GroupID FROM Groups WHERE GroupName='"+ groupName +"'";

			//Execution of the request
			rs = s.executeQuery(querySQL);

			//Each data of the matching tuples are put into a Hashtable and these Hashtable are put into a collection
			while(rs.next()) {

				Integer groupID = rs.getInt(1);

				j = this.conn.createStatement();
        this.addStatement(j);
				String querySQLGroupMember = "SELECT GroupMembersLogin FROM GroupMembers WHERE GroupID =" + groupID;
				//Execution of the request
				rsGroupMembersLogin = j.executeQuery(querySQLGroupMember);

				while(rsGroupMembersLogin.next()) {
					colLogin.add(rsGroupMembersLogin.getString(1));
				}
				rsGroupMembersLogin.close();
			}

			rs.close();
      this.conn.commit();
    }
    catch(SQLException sqle)
      {
            System.out.println(sqle);
    }
    // Return of the Collection
		return colLogin;
	}

	public void deleteFriend(String friendLogin) {
		// TODO delete a friend from DB
		// Actuellement c'est seulement enlever son login
		// de la base associative des membres de groupes
		
    PreparedStatement psDelete = null;

    try {
      psDelete = conn.prepareStatement("DELETE FROM GroupMembers WHERE GroupMembersLogin = ? ");
      this.addStatement(psDelete);

      psDelete.setString(1, friendLogin );
      psDelete.executeUpdate();
      System.out.println("Data deleted:"+ friendLogin);

      this.conn.commit();
      System.out.println("Committed the transaction");
    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void setUserInformation(String userName, String userFirstname, String userLogin, String userPassword, String userEmail, String userNickname, String userHideInSearch, String userHideEmail) {
    /**
			* Methode which insert the new user into the table USERS
      * TODO : Encrypter le password
      */
    PreparedStatement psInsert = null;

		try {
			psInsert = conn.prepareStatement("INSERT INTO Users(UserName, UserFirstname, UserLogin, UserPassword, UserEmail, UserNickname, UserHideInSearch, UserHideEmail) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
			this.addStatement(psInsert);

			psInsert.setString(1, userName );
			psInsert.setString(2, userFirstname);
			psInsert.setString(3, userLogin);
			psInsert.setString(4, userPassword);
			psInsert.setString(5, userEmail);
			psInsert.setString(6, userNickname);
			psInsert.setString(7, userHideInSearch);
			psInsert.setString(8, userHideEmail);

			psInsert.executeUpdate();
			System.out.println("User inserted: "+ userName+ " " + userFirstname);

			this.conn.commit();
			System.out.println("Committed the transaction");

    } catch (SQLException sqle) {
			System.out.println(sqle);
    }
		
  }

	public void updateUserInformation(String userName, String userFirstname, String userEmail, String userNickname, String hideInSearch, String hideEmail) {

		PreparedStatement psInsert = null;

    try {
      //Creation of the SQL request
        psInsert = conn.prepareStatement("UPDATE Users SET UserName = ? , UserFirstname = ? , UserEmail = ?, UserNickname = ?, UserHideInSearch = ?, UserHideEmail = ? WHERE UserId = 1");
        this.addStatement(psInsert);

				//Insert the data into the SQL request
        psInsert.setString(1, userName );
        psInsert.setString(2, userFirstname);
				psInsert.setString(3, userEmail);
				psInsert.setString(4, userNickname);
				psInsert.setString(5, hideInSearch);
				psInsert.setString(6, hideEmail);

				//Execute the SQL request
        psInsert.executeUpdate();
        System.out.println("User updated: "+ userName+ " " + userFirstname);

        this.conn.commit();
        System.out.println("Committed the transaction");
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }

	}

	public Map<String, String> existingUser(){

		Map<String,String>  result = new Hashtable();

    Statement s = null;
    ResultSet rs = null;

    try {
			//Creation and execution of the sql requete
      s = this.conn.createStatement();
      this.addStatement(s);

      rs = s.executeQuery("SELECT UserLogin, UserPassword FROM Users WHERE UserId = 1");

		  // get the user informations and put them into a Hashtable
      if (rs.next()) {

        String userLogin		 = rs.getString(1);
        String userPassword  = rs.getString(2);

				result.put("UserLogin"    , userLogin    );
				result.put("UserPassword" , userPassword );

				this.conn.commit();
        System.out.println("Committed the transaction");

			  return result; // return the result of the methode in a Hashtable
      }

			rs.close();
			this.conn.commit();
      System.out.println("Committed the transaction");
		}
    catch(SQLException sqle){
          System.out.println(sqle);
    }
    return null;
	}

  public Map<String,String> getUserInformation() {

    Map<String,String>  result = new Hashtable();

    Statement s = null;
    ResultSet rs = null;

    try {
			//Creation and execution of the sql requete
      s = this.conn.createStatement();
      this.addStatement(s);

      rs = s.executeQuery("SELECT UserName, UserFirstname, UserLogin, UserEmail, UserNickname, UserHideInSearch, UserHideEmail FROM Users WHERE UserId = 1");

		  // get the user informations and put them into a Hashtable
      if (rs.next()) {

				String userName				= rs.getString(1);
				String userFirstname  = rs.getString(2);
				String userLogin      = rs.getString(3);
				String userEmail      = rs.getString(4);
				String userNickname   = rs.getString(5);
				String userHideInSearch   = rs.getString(6);
				String userHideEmail   = rs.getString(7);

				result.put("UserName"      , userName      );
				result.put("UserFirstname" , userFirstname );
				result.put("UserLogin"     , userLogin     );
				result.put("UserEmail"     , userEmail     );
				result.put("UserNickname"  , userNickname  );
				result.put("UserHideInSearch", userHideInSearch);
				result.put("UserHideEmail" , userHideEmail );

				//System.out.println("Nom: "+ userName +" Prenom: "+ userFirstname + " Login: "+ userLogin + " Password:" + userPassword + " Email: " + userEmail +" Nick: " + userNickname);
      }

			rs.close();
      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }

	  return result; // return the result of the methode in a Hashtable

  }

	public Collection<Map> lookForAFile(String matchingWords) {

    //Initialisation of variables and arrays
    Collection<String> colMatchingWord = new Vector();
		Collection<Map> colMatchingFile = new Vector();
		String[] tabMatchingWord = null;

    int i=0;

    // Separation of matching word in the array tabMatchingWord
    StringTokenizer st =new StringTokenizer(matchingWords," ");
		while (st.hasMoreTokens()){
      colMatchingWord.add(st.nextToken());
    }

    Statement s = null;
    ResultSet rs = null;
    try {

      s = this.conn.createStatement();
      this.addStatement(s);
      StringBuffer querySQL = new StringBuffer ("SELECT FileID, FileName, FilePath, FileSize FROM File WHERE ");

			tabMatchingWord = colMatchingWord.toArray(new String[0]);

			for (i=0; i<tabMatchingWord.length; i++){
				if(i!=0){
					querySQL.append(" OR ");
				}
				querySQL.append("FileName LIKE '%" + tabMatchingWord[i] + "%'");
			}

			//Execution of the request
			rs = s.executeQuery(querySQL.toString());

			//Each data of the matching tuples are put into a Hashtable and these Hashtable are put into a collection
			while(rs.next()) {

				Map<String, String> result = new Hashtable();

        String fileID   = rs.getString(1);
        String fileName = rs.getString(2);
        String filePath = rs.getString(3);
        String fileSize = rs.getString(4);

				result.put("FileID"   , fileID  );
				result.put("FileName" , fileName);
				result.put("FilePath" , filePath);
				result.put("FileSize" , fileSize);

				colMatchingFile.add(result);

				//System.out.println("FileID: "+ fileID +" FileName: "+ fileName + " FilePath: "+ filePath + " FileSize:" + fileSize + " Bits.");
				}

			rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return colMatchingFile;
  }

	public Collection<Map<String,String>> getFileList() {

    //Initialisation of variables and arrays
		Collection<Map<String,String>> colFile = new Vector();

    Statement s = null;
    ResultSet rs = null;

    try {

      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT FileID, FileName, FilePath, FileSize FROM File";

			//Execution of the request
			rs = s.executeQuery(querySQL);

			//Each data of the matching tuples are put into a Hashtable and these Hashtable are put into a collection
			while(rs.next()) {

				Map<String, String> result = new Hashtable();

        String fileID   = rs.getString(1);
        String fileName = rs.getString(2);
        String filePath = rs.getString(3);
        String fileSize = rs.getString(4);

				result.put("FileID"   , fileID  );
				result.put("FileName" , fileName);
				result.put("FilePath" , filePath);
				result.put("FileSize" , fileSize);

				colFile.add(result);

				//System.out.println("FileID: "+ fileID +" FileName: "+ fileName + " FilePath: "+ filePath + " FileSize:" + fileSize + " Bits.");
				}

			rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return colFile;
  }

	public Collection<Map> getFileInfoById(int fileId) {

    //Initialisation of variables and arrays
		Collection<Map> colFile = new Vector();

    Statement s = null;
    ResultSet rs = null;

    try {

      //Preparation of a SQL-Select request which select all the data of the table File
      s = this.conn.createStatement();
      this.addStatement(s);
      String querySQL = "SELECT FileName, FilePath, FileSize FROM File WHERE FileID =" + fileId;

			//Execution of the request
			rs = s.executeQuery(querySQL);

			//Each data of the matching tuples are put into a Hashtable and these Hashtable are put into a collection
			while(rs.next()) {

				Map<String, String> result = new Hashtable();

        String fileName = rs.getString(1);
        String filePath = rs.getString(2);
        String fileSize = rs.getString(3);

				result.put("FileName" , fileName);
				result.put("FilePath" , filePath);
				result.put("FileSize" , fileSize);

				colFile.add(result);

				}

			rs.close();
      this.conn.commit();
      }
      catch(SQLException sqle)
        {
            System.out.println(sqle);
        }
    // Return of the Collection
		return colFile;
  }

	public void addNewFile(String fileName, String filePath, Integer fileSize) {

    PreparedStatement psInsert = null;

    try {
      psInsert = conn.prepareStatement("INSERT INTO File (FileName, FilePath, FileSize) VALUES(?, ?, ?)");
      this.addStatement(psInsert);

      psInsert.setString(1, fileName );
      psInsert.setString(2, filePath);
      psInsert.setInt(3, fileSize);
      psInsert.executeUpdate();
      System.out.println("Data inserted:"+ fileName);

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

    public void addNewFolder(Collection<String[]> listOfFiles) {

    PreparedStatement psInsert = null;

    try {
      psInsert = conn.prepareStatement("INSERT INTO File (FileName, FilePath, FileSize) VALUES(?, ?, ?)");
      this.addStatement(psInsert);

      Iterator ite = listOfFiles.iterator();
      while (ite.hasNext()){
          String[] fileDescription = (String[]) ite.next();
          psInsert.setString(1, fileDescription[1] );
          psInsert.setString(2, fileDescription[0]);
          psInsert.setInt(3, Integer.parseInt(fileDescription[2]));
          psInsert.executeUpdate();
          System.out.println("Data inserted:"+ fileDescription[0]);
      }
      

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void deleteSharedFileList(){
		Statement s = null;

    try {
      s = this.conn.createStatement();
      this.addStatement(s);

			s.execute("DROP TABLE File");
      System.out.println("Dropped table File");

      s.execute("CREATE TABLE File(FileId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, FileName VARCHAR(200), FilePath VARCHAR(250), FileSize BIGINT, PRIMARY KEY (FileId))");
      System.out.println("Created table File");

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

	public void deleteFile(String fileName){

		PreparedStatement psDelete = null;

    try {
      psDelete = conn.prepareStatement("DELETE FROM File WHERE FileName = ?");
      this.addStatement(psDelete);

      psDelete.setString(1, fileName );
      psDelete.executeUpdate();
      System.out.println("Data deleted:"+ fileName);

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
	}

  public void createEmbeddedDatabase() {

    Statement s = null;

    try {
      //Creation of the 5 table of the embedded database
      s = this.conn.createStatement();
      this.addStatement(s);

      s.execute("CREATE TABLE File(FileId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, FileName VARCHAR(200), FilePath VARCHAR(250), FileSize BIGINT, PRIMARY KEY (FileId))");
      System.out.println("Created table File");

      s.execute("CREATE TABLE Notification(NotificationId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, NotificationContent VARCHAR(200), NotificationType INTEGER, NotificationDate BIGINT, NotificationReceiver VARCHAR(40), NotificationSenderLogin VARCHAR(40), NotificationSenderNickname VARCHAR(40), PRIMARY KEY (NotificationId))");
      System.out.println("Created table Notification");

			s.execute("CREATE TABLE Users(UserId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, UserName VARCHAR(20), UserFirstname VARCHAR(20), UserLogin VARCHAR(20), UserPassword VARCHAR(40), UserEmail VARCHAR(40), UserNickname VARCHAR(40), UserHideInSearch VARCHAR(6), UserHideEmail VARCHAR(6), PRIMARY KEY (UserId))");
      System.out.println("Created table Users");

			//TODO Change the name of the table add the constraint "FOREIGN KEY ('GroupId') REFERENCES 'Group'('GroupId')"
			s.execute("CREATE TABLE Groups(GroupId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, GroupName VARCHAR(20), PRIMARY KEY (GroupId))");
      System.out.println("Created table Groups");

			s.execute("CREATE TABLE GroupMembers(GroupMembersId INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, GroupId INT, GroupMembersLogin VARCHAR(20), PRIMARY KEY (GroupMembersId), FOREIGN KEY (GroupId) REFERENCES Groups(GroupId))");
      System.out.println("Created table GroupMembers");

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
  }

	public void eraseEmbeddedDatabase() {

    System.out.println("Erase the " + framework + " Derby Database");

		Statement s = null;

		try {
      //Creation of the 5 table of the embedded database
      s = this.conn.createStatement();
      this.addStatement(s);

      s.execute("DROP TABLE File");
      System.out.println("Droped table File");

      s.execute("DROP TABLE Notification");
      System.out.println("Droped table Notification");

			s.execute("DROP TABLE Users");
      System.out.println("Droped table Users");

			s.execute("DROP TABLE GroupMembers");
      System.out.println("Droped table GroupMembers");

			s.execute("DROP TABLE Groups");
      System.out.println("Droped table Groups");

      this.conn.commit();
      System.out.println("Committed the transaction");

    }
    catch(SQLException sqle)
      {
          System.out.println(sqle);
      }
  }

	private void addStatement(Statement s) throws SQLException {
		// closes old statements
		Statement[] states = this.statements.keySet().toArray(new Statement[0]);
		for (Statement st: states) {
			Date d = this.statements.get(st);
			if ( (new Date().getTime() - d.getTime()) > 4*1000 ) {
				st.close();
				this.statements.remove(st);
			}
		}

		// adds the new statement in the hashtable
		this.statements.put(s, new Date());
	}

  public void shutdownDatabase() {

    try {
			this.conn.close();
      // the shutdown=true attribute shuts down Derby
      DriverManager.getConnection("jdbc:derby:;shutdown=true");

      // To shut down a specific database only, but keeep the
      // engine running (for example for connecting to other
      // databases), specify a database in the connection URL:
      //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
     }
     catch (SQLException se) {

       if (( (se.getErrorCode() == 50000) && ("XJ015".equals(se.getSQLState()) ))) {

         // we got the expected exception
         System.out.println("Derby shut down normally");
         // Note that for single database shutdown, the expected
         // SQL state is "08006", and the error code is 45000.
       } else {
         // if the error code or SQLState is different, we have
         // an unexpected exception (shutdown failed)
         System.err.println("Derby did not shut down normally: " + se);
       }
     }
   }

	private void loadDriver() {
		/*
		 *  The JDBC driver is loaded by loading its class.
		 *
		 *  In an embedded environment, this will also start up the Derby
		 *  engine (though not any databases), since it is not already
		 *  running. In a client environment, the Derby engine is being run
		 *  by the network server framework.
		 *
		 *  In an embedded environment, any static Derby system properties
		 *  must be set before loading the driver to take effect.
		 */
		try {
				Class.forName(driver).newInstance();
				System.out.println("Loaded the appropriate driver");
		} catch (ClassNotFoundException cnfe) {
				System.err.println("\nUnable to load the JDBC driver " + driver);
				System.err.println("Please check your CLASSPATH.");
				cnfe.printStackTrace(System.err);
		} catch (InstantiationException ie) {
				System.err.println(
										"\nUnable to instantiate the JDBC driver " + driver);
				ie.printStackTrace(System.err);
		} catch (IllegalAccessException iae) {
				System.err.println(
										"\nNot allowed to access the JDBC driver " + driver);
				iae.printStackTrace(System.err);
		}
	}

}
